package sanrenxing.dayEnd.backRepertoryTotal.dao;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import sanrenxing.dayEnd.backRepertoryTotal.domain.*;

import java.util.List;

public interface EarningAlipayDao {

    /**
     * 显示现金收益支付数据
     * @return
     */
    @Select("<script>" +
            "select * from " +
            " (select rownum rn,ss.* from " +
            "(select ac.accName,'现金利息' as businType,sysdate as businTime,money as expMoney,cr.capflow from " +
            "cashReceivable cr join cashAccount ac on cr.caId=ac.caId  where cr.businType='存款利息') ss" +
            " <where>#{end}>rownum</where>)" +
            "<where>rn>=#{start}</where>" +
            "</script>")
    public List<CashAlipay> queryCash(@Param("start")int start , @Param("end")int end);

    /**
     * 收益支付现金利息行数
     * @return
     */
    @Select("select count(*) from cashReceivable caps join cashAccount ac on caps.caId=ac.caId where caps.busintype='存款利息'")
    public int queryCashCount();

    /**
     * 两费收益支付的显示
     * @param start
     * @param end
     * @return
     */
    @Select("<script>" +
            "select * from " +
            " (select rownum rn,ss.* from " +
            "(select ca.accName,cr.businType,sysdate as businTime,cr.capflow as dofa,cr.money as expMoney from " +
            "cashReceivable cr join cashAccount ca on cr.caId=ca.caId where cr.businType='管理费' or cr.businType='托管费') ss" +
            " <where>#{end}>rownum</where>)" +
            "<where>rn>=#{start}</where>" +
            "</script>")
    public List<TwoAlipay> queryTwo(@Param("start")int start , @Param("end")int end);

    /**
     * 两费行数
     * @return
     */
    @Select("select count(*) from cashReceivable cr join cashAccount ca on cr.caId=ca.caId where cr.businType='管理费' or cr.businType='托管费'")
    public int twoCount();

    /**
     * 债券计息收益支付显示页面
     * @param start
     * @param end
     * @return
     */
    @Select("<script>" +
            "select * from " +
            " (select rownum rn,ss.* from " +
            "(select sif.bondName,sif.bondType,sysdate as businDate,rap.capflow as dofa,rap.money as expMoney from " +
            "receivableAndPayable rap join securitiesInformation sif on rap.seId=sif.bondId where rap.businType='债券利息') ss" +
            " <where>#{end}>rownum</where>)" +
            "<where>rn>=#{start}</where>" +
            "</script>")
    public List<BondAlipay> queryBond(@Param("start")int start ,@Param("end")int end);

    /**
     * 行数
     * @return
     */
    @Select("select count(*) from receivableAndPayable rap join securitiesInformation sif on rap.seId=sif.bondId where rap.businType='债券利息'")
    public int bondCount();

    /**
     * 现金利息和两费的支付
     */
    @Select("call cash_Alipay()")
    public void cashAlipay();

    /**
     * 债券支付
     */
    @Select("call bond_Alipay()")
    public void bondAlipay();

    /**
     * 将当天需要支付利息的存款业务取出
     * @return
     */
    @Select("select (db.expiryDate-db.businTime) as dayCount,ca.cardRate,db.depMoney,db.caIds as caId,db.fundId from " +
            "depositBusiness db join cashAccount ca on ca.caId=db.caIds where expiryDate=to_date('${toDay}','yyyy-MM-dd') and db.handIf=1")
    public List<AlipayCash> alipayCash(String toDay);

    /**
     * 将存款业务到期的数据删除
     * @param toDay
     */
    @Delete("delete from depositBusiness where expiryDate=to_date('${toDay}','yyyy-MM-dd') and handIf=1")
    public void deleteAlipayCash(@Param("toDay") String toDay);

    /**
     * 删除现金应收应付的存款利息
     * @param rate
     * @param caId
     * @param businType
     * @param dayCount
     */
    @Delete("delete from cashReceivable b " +
            " where exists (select 1 " +
            "                 from (select rownum rn,cashReceivable.Id,cashReceivable.businType,cashReceivable.money,cashReceivable.caId from cashReceivable" +
            "                  where cashReceivable.businType=#{businType} and cashReceivable.money=#{rate} and caId=#{caId}) aa " +
            "                where aa.rn >= 0 " +
            "                  and aa.rn <= #{dayCount}" +
            "                  and b.Id=aa.Id" +
            "                  ) ")
    public void deleteSec(@Param("rate") double rate,@Param("caId")String caId,@Param("businType")String businType,@Param("dayCount")int dayCount);

    /**
     * 存款业务时现金应收应付添加到资金调拨
     */
    @Select("select fundId,caId,'存款利息' as reType,money as reMoney,sysdate as businDate,sysdate as reDate from cashReceivable  where " +
            "cashReceivable.businType='存款利息' and cashReceivable.money=#{money} and caId=#{caId} and rownum<=#{dayCount}")
    public List<FundAllocation> addFA(@Param("money") double money, @Param("caId") String caId, @Param("dayCount") int dayCount);

    /**
     * 取得现金库存的账户id
     * @return
     */
    @Select("select caId from cashStock")
    public List<Integer> cashStockId();

    /**
     * 取出现金应收应付的现金库存所产生的利息
     * @param caId
     * @return
     */
    @Select("select fundId,caId,money as reMoney from cashReceivable where caId=#{caId} and businType='存款利息'")
    public List<FundAllocation> queryCashStockSec(@Param("caId") String caId);

    /**
     * //删除现金应收应付中现金库存产生的利息数据
     * @param caId
     */
    @Delete("delete from cashReceivable where caId=#{caId} and businType='存款利息'")
    public void deleteCashStockSec(@Param("caId") String caId);

    /**
     * 债券利息的支付：查询需付息债券
     */
    @Select("select sif.bondId as seId,sif.beginInte,sif.endInte,sif.noipNum,(round(to_number(sif.endInte-sif.beginInte))) as dayCount from " +
            "securitiesStock ss join securitiesInformation sif on ss.seId=sif.bondId")
    public List<BondAlipaySec> queryBondId();

    /**
     * 债券利息的证券应收应付在收益支付时添加到资金调拨
     */
    @Select("select fundId,caId,money as reMoney,sysdate as businDate,sysdate as reDate from " +
            "receivableAndPayable where seId=#{seId} and businType='债券利息'")
    public List<FundAllocation> addBondCash(@Param("seId") String seId);

    /**
     * 债券利息的证券应收应付在收益支付时删除证券应收应付数据
     */
    @Delete("delete from receivableAndPayable where seId=#{seId} and businType='债券利息'")
    public void deleteBondCash(@Param("seId") String seId);

    /**
     * 当债券到期后，应添加债券的应收资金调拨,这里是查询资金调拨
     * @return
     */
    @Select("select fundId,caId,sumMoney as reMoney,sysdate as businDate,sysdate as reDate from " +
            "securitiesStock where seId=#{seId}")
    public List<FundAllocation> addBondFund(@Param("seId") String seId);

    /**
     * 在证券库存中查询到期的债券id
     * @return
     */
    @Select("select sif.bondId from " +
            "securitiesStock ss join securitiesInformation sif on ss.seId=sif.bondId where endInte=to_date('${toDay}','yyyy-MM-dd')")
    public List<String> queryBondStockId(String toDay);

    /**
     * 债券到期后删除证券库存中的债券
     * @param seId
     */
    @Delete("delete from securitiesStock where seId=#{seId}")
    public void deleteBondStock(@Param("seId") String seId);

    /**
     * 收益支付支付时添加两费到资金调拨
     */
    @Select("select fundId,caId,money as reMoney,businType as reType,sysdate as reDate,sysdate as businDate from " +
            "cashReceivable where businType='托管费' or businType='管理费'")
    public List<FundAllocation> addTwoMoney();

    /**
     * 收益支付时删除两费的现金应收应付数据
     */
    @Delete("delete from cashReceivable where businType='托管费' or businType='管理费'")
    public void deleteTowMoney();

}
